package com.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;

import com.log.LogUtil;


public class DBUtil {
	
	private static Properties props=new Properties();
	static {
		try {
			props.load(DBUtil.class.getResourceAsStream("/com/config/config.properties"));			
			Class.forName(props.getProperty("driverClass"));
		} catch (Exception e) {
			LogUtil.warn(e);
		}
	}

	public static void closeConnection(Connection con) throws SQLException {
		if (con != null) {
			con.close();
		}
	}

	public static void closeConnectionAndStatement(Connection con, Statement st)
			throws SQLException {
		closeStatement(st);
		closeConnection(con);
	}

	public static void closeConnectionAndStatementAndResultSet(Connection con,
			Statement st, ResultSet rs) throws SQLException {
		closeResultSet(rs);
		closeStatement(st);
		closeConnection(con);
	}

	public static void closeInputStream(InputStream is) throws IOException {
		if (is != null) {
			is.close();
		}
	}

	public static void closeOutputStream(OutputStream os) throws IOException {
		if (os != null) {
			os.close();
		}
	}

	public static void closeResultSet(ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
		}
	}

	public static void closeStatement(Statement st) throws SQLException {
		if (st != null) {
			st.close();
		}
	}

	public static void closeStatementAndResultSet(Statement st, ResultSet rs)
			throws SQLException {
		closeResultSet(rs);
		closeStatement(st);
	}

	public static boolean createDataTable(boolean replace, String tableName,String[] fields, String[] foreignKey) {
		StringBuilder sb = new StringBuilder();
		if (StringUtils.isBlank(tableName))
			return false;
		if (null == fields || fields.length < 1)
			return false;
		for (int i = 0; i < fields.length; i++) {
			sb.append(fields[i]).append(",");
		}
		if (null != foreignKey && foreignKey.length > 0)
			for (int i = 0; i < foreignKey.length; i++) {
				sb.append(foreignKey[i]).append(",");
			}
		if (sb.length() > 0)
			sb.deleteCharAt(sb.length() - 1).insert(0, "create table if not exists " + tableName + "(").append(")");
		Connection con = null;
		Statement st = null;
		try {
			con = getConnnection();
			con.setAutoCommit(false);
			st = con.createStatement();
			if (replace)
				st.addBatch("DROP TABLE IF EXISTS " + tableName);
			else {
				// String
				// databaseNameSql="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+con.getCatalog()+"' AND TABLE_NAME='"+tableName+"'";
				// ResultSet
				// rs=getStatement().executeQuery("show tables like '"+tableName+"'");
				// if(rs!=null && rs.next()) return false;//表已经存在
			}
			st.addBatch(sb.toString());
			st.executeBatch();
			con.commit();
			return true;
		} catch (SQLException e) {
			LogUtil.error(e);
			try {
				con.rollback();
			} catch (SQLException e1) {
				LogUtil.error(e1);
			}
		} finally {
			try {
				closeStatement(st);
				closeConnection(con);
			} catch (SQLException e) {
				LogUtil.error(e);
			}
		}
		return false;
	}

	public static Connection getConnnection() throws SQLException {
		return getConnnection(true);
	}

	public static Connection getConnnection(boolean mySelf) throws SQLException {
		Connection con = null;
		if (mySelf) {
			con = DriverManager.getConnection(props.getProperty("url"),props.getProperty("username"), props.getProperty("password"));
		} else {
			con = getConnnection();
		}
		return con;
	}

	public static Statement getStatement(Connection con) throws SQLException {
		return con.createStatement();
	}

	public static Statement getStatement(Connection con, int resultSetType,
			int resultSetConcurrency) throws SQLException {
		return con.createStatement(resultSetType, resultSetConcurrency);
	}

	public static int insert(List<Map<String, ?>> list) throws SQLException {
		int count = 0;
		if (list.size() == 0) return 0;
		Connection con = getConnnection();
		Statement st = con.createStatement();
		for (Map<String, ?> map : list) {
			StringBuffer fieldSql = new StringBuffer();
			StringBuffer valueSql = new StringBuffer();
			Iterator<String> it = map.keySet().iterator();
			if (!map.containsKey("tableName")) {
				throw new SQLException("tableName must be contained in map's keyset when insert ");
			}
			String tableName = map.get("tableName").toString();
			while (it != null && it.hasNext()) {
				String key = it.next();
				if (key.equals("tableName")) continue;
				fieldSql.append(key).append(",");
				valueSql.append(map.get(key) == null ? "null" : "'"+ map.get(key).toString() + "'").append(",");
			}
			if (fieldSql.length() > 0 && valueSql.length() > 0) {
				String sql = fieldSql.deleteCharAt(fieldSql.length() - 1)
						.insert(0, "insert into " + tableName + "(").append(") values (")
						.append(valueSql.deleteCharAt(valueSql.length() - 1)).append(")").toString();
				st.addBatch(sql);
			}
		}
		int total[] = st.executeBatch();
		for (int i : total) {
			count += i;
		}
		closeConnection(con);
		return count;
	}

	public static int insert(Map<String, ?> map) throws SQLException {
		int count = 0;
		StringBuffer fieldSql = new StringBuffer();
		StringBuffer valueSql = new StringBuffer();
		Iterator<String> it = map.keySet().iterator();
		if (!map.containsKey("tableName")) {
			throw new SQLException("tableName must be contained in map's keyset when insert ");
		}
		String tableName = map.get("tableName").toString();
		while (it != null && it.hasNext()) {
			String key = it.next();
			if (key.equals("tableName")) continue;
			fieldSql.append(key).append(",");
			valueSql.append(map.get(key) == null ? "null" : "'"	+ map.get(key).toString() + "'").append(",");
		}
		if (fieldSql.length() > 0 && valueSql.length() > 0) {
			String sql = fieldSql.deleteCharAt(fieldSql.length() - 1)
					.insert(0, "insert into " + tableName + "(").append(") values (")
					.append(valueSql.deleteCharAt(valueSql.length() - 1)).append(")").toString();
			count = update(sql);
		}
		return count;
	}

	public static int insert(Map<String, Object> map, String[] except)
			throws SQLException {
		int count = 0;
		String tableName = map.get("tableName").toString();
		Iterator<String> it = map.keySet().iterator();
		StringBuffer fields = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		while (null != it && it.hasNext()) {
			String key = it.next();
			if (ArrayUtils.contains(except, key)) continue;
			Object val = map.get(key);
			String valSql = "";
			if (val instanceof String) {
				valSql = "'" + val.toString() + "'";
			} else {
				valSql = val == null ? "null" : val.toString();
			}
			fields.append(key).append(",");
			vals.append(StringUtils.isBlank(valSql) ? "null" : valSql).append(",");
		}
		if (fields.length() > 0){
			fields.deleteCharAt(fields.length() - 1);
		}
		if (vals.length() > 0){
			vals.deleteCharAt(vals.length() - 1);
		}
		String sql = "insert into " + tableName + "(" + fields.toString() + ")"	+ "values(" + vals.toString() + ")";
		count = update(sql);
		return count;
	}

	public static int insert(String sql) throws SQLException {
		return update(sql);
	}

	public static int queryForInt(String sql) throws SQLException {
		int count = 0;
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnnection();
		st = getStatement(con);
		rs = st.executeQuery(sql);
		if (null != rs && rs.next()) {
			if (rs.last()) {
				count = rs.getInt(1);
			} else {
				throw new SQLException("expected 1 row by many");
			}
		}
		closeResultSet(rs);
		closeStatement(st);
		closeConnection(con);
		return count;
	}

	@SuppressWarnings("unchecked")
	public static <T> List<T> queryForList(Connection con, String sql, Class<T> c) throws SQLException {
		List<T> list = new ArrayList<T>();
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(sql);
		while (rs != null && rs.next()) {
			list.add((T) rs.getObject(1));
		}
		closeStatementAndResultSet(st, rs);
		return list;
	}

	public static <T> List<T> queryForList(String sql, Class<T> c)	throws SQLException {
		List<T> list = new ArrayList<T>();
		Connection con = getConnnection();
		list = queryForList(con, sql, c);
		closeConnection(con);
		return list;
	}

	public static List<Map<String, Object>> queryForListMap(Connection con,	String sql) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Statement st = getStatement(con);
		ResultSet rs = st.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		while (null != rs && rs.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 1; i <= columnCount; i++) {
				map.put(rsmd.getColumnName(i), rs.getObject(i));
			}
			list.add(map);
		}
		closeResultSet(rs);
		closeStatement(st);
		return list;
	}

	@SuppressWarnings("unchecked")
	public static <T> List<Map<String, T>> queryForListMap(Connection con,
			String sql, Class<T> c) throws SQLException {
		List<Map<String, T>> list = new ArrayList<Map<String, T>>();
		ResultSet rs = con.createStatement().executeQuery(sql);
		ResultSetMetaData rmd = rs == null ? null : rs.getMetaData();
		while (rs != null && rs.next()) {
			Map<String, T> map = new HashMap<String, T>();
			for (int i = 1; i <= rmd.getColumnCount(); i++) {
				map.put(rmd.getColumnName(i), (T) rs.getObject(i));
			}
			list.add(map);
		}
		return list;
	}

	public static List<Map<String, Object>> queryForListMap(String sql)	throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection con = getConnnection();
		list = queryForListMap(con, sql);
		closeConnection(con);
		return list;
	}

	public static <T> List<Map<String, T>> queryForListMap(String sql,Class<T> c) throws SQLException {
		List<Map<String, T>> list = new ArrayList<Map<String, T>>();
		Connection con = getConnnection();
		list = queryForListMap(con, sql, c);
		closeConnection(con);
		return list;
	}

	@SuppressWarnings("unchecked")
	public static <T> List<T> queryForListOne(String sql, Class<T> c) throws SQLException {
		List<T> list = new ArrayList<T>();
		Connection con = getConnnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		if (rs != null && rs.next()) {
			if (rs.last()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					list.add((T) rs.getObject(i));
				}
			} else {
				throw new SQLException("expected 1 row but many");
			}
		}
		closeConnection(con);
		return list;
	}

	public static Map<String, Object> queryForMap(Connection con, String sql) throws SQLException {
		Statement st = null;
		ResultSet rs = null;
		Map<String, Object> map = new HashMap<String, Object>();
		st = getStatement(con);
		rs = st.executeQuery(sql);
		if (null != rs && rs.next()) {
			if (rs.last()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)
					map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
			} else {
				throw new SQLException("expected 1 row but many");
			}
		}
		closeResultSet(rs);
		closeStatement(st);
		return map;
	}

	public static Map<String, Object> queryForMap(PreparedStatement ps)	throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		ResultSet rs = ps.executeQuery();
		if (null != rs && rs.next()) {
			if (rs.last()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++){
					map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
				}
			} else {
				throw new SQLException("expected 1 row but many");
			}
		}
		closeResultSet(rs);
		closeStatement(ps);
		return map;
	}

	public static Map<String, Object> queryForMap(String sql) throws SQLException {
		Connection con = getConnnection();
		Map<String, Object> map = queryForMap(con, sql);
		closeConnection(con);
		return map;
	}

	@SuppressWarnings("unchecked")
	public static <T> Map<String, T> queryForMap(String sql, Class<T> c) throws SQLException {
		Map<String, T> map = new HashMap<String, T>();
		Connection con = getConnnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		if (rs != null && rs.next()) {
			if (rs.last()) {
				ResultSetMetaData rmd = rs.getMetaData();
				for (int i = 1; i <= rmd.getColumnCount(); i++) {
					map.put(rmd.getColumnName(i), (T) rs.getObject(i));
				}
			} else {
				throw new SQLException("expected 1 row but many");
			}
		}
		closeConnection(con);
		return map;
	}

	@SuppressWarnings("unchecked")
	public static <T> Map<String, T> queryForMaps(String sql, Class<T> c) throws SQLException {
		Map<String, T> map = new HashMap<String, T>();
		Connection con = getConnnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		while (rs != null && rs.next()) {
			map.put(rs.getString(1), (T) rs.getObject(2));
		}
		closeConnection(con);
		return map;
	}

	@SuppressWarnings("unchecked")
	public static <T> T queryForObject(Connection con, String sql, Class<T> c)	throws SQLException {
		T t = null;
		Statement st = getStatement(con);
		ResultSet rs = st.executeQuery(sql);
		if (null != rs && rs.next()) {
			if (rs.last()) {
				t = (T) rs.getObject(1);
			} else {
				throw new SQLException("expected 1 row but many");
			}
		}
		closeResultSet(rs);
		closeStatement(st);
		return t;
	}

	public static <T> T queryForObject(String sql, Class<T> c) throws SQLException {
		T t = null;
		Connection con = getConnnection();
		t = queryForObject(con, sql, c);
		closeConnection(con);
		return t;
	}

	public static ResultSet queryForResultSet(Connection con, String sql) throws SQLException {
		return con.createStatement().executeQuery(sql);
	}

	public static String queryForString(String sql) throws SQLException {
		return queryForObject(sql, String.class);
	}

	public static int update(Connection con, String sql) throws SQLException {
		int count = 0;
		Statement st = getStatement(con);
		count = st.executeUpdate(sql);
		closeStatement(st);
		return count;
	}

	public static int update(Map<String, ?> map, String whereStr) throws SQLException {
		int count = 0;
		StringBuilder sql = new StringBuilder();
		Iterator<String> it = map.keySet().iterator();
		if (!map.containsKey("tableName")) {
			throw new SQLException("tableName must be contained in map's keyset when update ");
		}
		String tableName = map.get("tableName").toString();
		while (it != null && it.hasNext()) {
			String key = it.next();
			if (key.equals("tableName")) continue;
			sql.append(key).append("=").append(map.get(key) == null ? "null" : "'"+ map.get(key).toString() + "'").append(",");
		}
		if (sql.length() > 0) {
			sql.insert(0, "update " + tableName + " set ").deleteCharAt(sql.length() - 1).append(" where 1=1 ").append(whereStr);
			count = update(sql.toString());
		}
		return count;
	}

	public static int update(String sql) throws SQLException {
		int count = 0;
		Connection con = null;
		count = update(con, sql);
		return count;
	}

	public static boolean updateBatchSql(List<String> list, Connection con) {
		Statement st = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			for (int i = 0; i < list.size(); i++) {
				st.addBatch(list.get(i));
			}
			st.executeBatch();
			con.commit();
			return true;
		} catch (SQLException e) {
			LogUtil.warn(e.getMessage());
			try {
				con.rollback();
			} catch (SQLException e1) {
				LogUtil.warn(e1.getMessage());
			}
		} finally {
			try {
				closeStatement(st);
			} catch (SQLException e) {
				LogUtil.warn(e.getMessage());
			}
		}
		return false;
	}
	public void test() {
		/*
		 * try { System.out.println("properties:"+getConn().getClientInfo());
		 * DatabaseMetaData dbm=getConn().getMetaData();
		 * System.out.println("catalog:"+getConn().getCatalog());
		 * System.out.println
		 * ("properties:"+dbm.getDatabaseProductName()+","+dbm.
		 * getDatabaseMajorVersion()
		 * +","+dbm.getDatabaseMinorVersion()+","+dbm.getDatabaseProductVersion
		 * ()); String sql=
		 * "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"
		 * +getConn().getCatalog()+"' AND TABLE_NAME='tttt'";
		 * sql="show tables like 'tttt'"; ResultSet rs
		 * =getStatement().executeQuery(sql); while(null!=rs && rs.next()){
		 * System.out.println("status:"+rs.getString(1)); } } catch
		 * (SQLException e) {
		 * 
		 * }
		 */
		/*createDataTable(true, "testAccountLogs", new String[] {
				"id int primary key AUTO_INCREMENT", "organid int",
				"userid varchar(50)", "maker varchar(50)",
				"userBlocked int default 0", "organBlocked int default 0" },
				null);
		*/
	}


}
